﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Imports log4net

Partial Class Report_RP08
    Inherits System.Web.UI.Page

    Dim Report As Object
    Public ScriptText As String
    Private Shared logger As ILog = LogManager.GetLogger("Report_RP08")

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            ScriptText = "<script> wclose();</script>"
        End If
    End Sub
    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012"

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2

        If Session("users") Is Nothing Then
            Response.Redirect("../LoginForm.aspx")
        End If


        Getdata()


    End Sub


    Function Getdata() As Decimal


        Dim adapter As New Data_ReportTableAdapters.R08TableAdapter
        Dim sql As String = ""
        Dim dt As New Data_Report.R08DataTable

        sql += " select A.Fundcenter_FundcenterCode + ' ' + fundcenters.description as Fundcenter_FundcenterCode ,A.project"
        sql += " , A.ActivityType_ActivityTypeCode, A.Description, SUM(A.NewQuantity) AS NewQuantity, SUM(A.ReplaceQuantity) AS ReplaceQuantity"
        sql += " , SUM(A.SumQuantity) AS SumQuantity, AVG(A.PricePerUnit) AS PricePerUnit , SUM(A.Amount) AS Amount, A.Version, A.PlanYear, A.AssetGroup, A.AssetGroupName "
        sql += " from ("


        sql += "("

        sql += "   SELECT Budgetdetails.Id, BudgetDetails.Fundcenter_FundcenterCode,case when BudgetDetails.project_projectcode is null then 'งบประมาณประจำ' else (case LEFT(BudgetDetails.project_projectcode,1) WHEN 'P' THEN 'งบประมาณโครงการ' WHEN 'D' THEN 'งบประมาณสนับสนุน' END) end as project,"
        sql += "   BudgetDetails.ActivityType_ActivityTypeCode, ActivityTypes.Description,"
        sql += "   isnull(BudgetDetails.NewQuantity,0) AS NewQuantity, isnull(BudgetDetails.ReplaceQuantity,0) AS ReplaceQuantity,"
        sql += "  (isnull(BudgetDetails.NewQuantity,0) + isnull(BudgetDetails.ReplaceQuantity,0)) AS SumQuantity,"
        sql += "   BudgetDetails.PricePerUnit, BudgetDetails.Amount, BudgetDetails.Version, BudgetDetails.PlanYear, ActivityTypes.AssetGroup, "

        sql += "  (SELECT AppConfigs.Description FROM AppConfigs WHERE AppConfigs.Name = 'ASSET_GROUP' AND AppConfigs.Value = ActivityTypes.AssetGroup)as AssetGroupName "

        sql += "  FROM Budgetdetails INNER JOIN ActivityTypes "
        sql += "  ON Budgetdetails.ActivityType_ActivityTypeCode = ActivityTypes.ActivityTypeCode"


        sql += "   WHERE ActivityType_ActivityTypeCode Is Not NULL And"
        sql += " ((Budgetdetails.Version = '" & Session("version1") & "' And Budgetdetails.PlanYear = " & Session("year1") & ") "

        sql += " and (ActivityTypes.PlanYear = " & Session("year1") & "))"



        If Session("FCTRmode") = "1" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If

        'Comment By Orn 18/08/2558
        'If Val(Session("ActivityS")) <> 0 And Val(Session("ActivityE")) <> 0 Then
        '    sql += "     and  ActivityTypes between " & Session("ActivityS") & " And " & Session("ActivityE")

        'End If

        'Add By Orn 18/08/2558
        If Session("ActivityTypeS") <> "" Then
            sql += " and ActivityType_ActivityTypeCode >= " & Session("ActivityTypeS")
        End If
        If Session("ActivityTypeE") <> "" Then
            sql += " and ActivityType_ActivityTypeCode <= " & Session("ActivityTypeE")
        End If



        If Val(Session("AssetGroup")) <> 0 Then
            sql += " and  ActivityTypes.AssetGroup = " & Session("AssetGroup")
        End If


        sql += "UNION"

        sql += "   SELECT Budgetdetails.Id, BudgetDetails.Fundcenter_FundcenterCode,case when BudgetDetails.project_projectcode is null then 'งบประมาณประจำ' else (case LEFT(BudgetDetails.project_projectcode,1) WHEN 'P' THEN 'งบประมาณโครงการ' WHEN 'D' THEN 'งบประมาณสนับสนุน' END) end as project,"
        sql += "   AssetNo AS ActivityType_ActivityTypeCode, AssetDescription AS Description,"
        sql += "   isnull(BudgetDetails.NewQuantity,0) AS NewQuantity, isnull(BudgetDetails.ReplaceQuantity,0) AS ReplaceQuantity,"
        sql += "  (isnull(BudgetDetails.NewQuantity,0) + isnull(BudgetDetails.ReplaceQuantity,0)) AS SumQuantity,"
        sql += "   BudgetDetails.PricePerUnit, BudgetDetails.Amount, BudgetDetails.Version, BudgetDetails.PlanYear, AssetRowNo AS AssetGroup, "

        sql += "  (SELECT AppConfigs.Description FROM AppConfigs WHERE AppConfigs.Name = 'ASSET_GROUP' AND AppConfigs.Value = Budgetdetails.AssetRowNo)as AssetGroupName "

        sql += "  FROM Budgetdetails"


        sql += "   WHERE ActivityType_ActivityTypeCode Is NULL And"
        sql += " Budgetdetails.Version = '" & Session("version1") & "' And Budgetdetails.PlanYear = " & Session("year1") & " "
        sql += " AND Left(AssetNo,1) = '9' "


        If Session("FCTRmode") = "1" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If



        If Val(Session("AssetGroup")) <> 0 Then
            sql += " and  AssetRowNo = " & Session("AssetGroup")
        End If
        sql += ")"

        sql += " UNION ALL"

        sql += "("
        sql += "   SELECT Budgetdetails.Id, BudgetDetails.Fundcenter_FundcenterCode,case when BudgetDetails.project_projectcode is null then 'งบประมาณประจำ' else (case LEFT(BudgetDetails.project_projectcode,1) WHEN 'P' THEN 'งบประมาณโครงการ' WHEN 'D' THEN 'งบประมาณสนับสนุน' END) end as project,"
        sql += "   BudgetDetails.ActivityType_ActivityTypeCode, ActivityTypes.Description,"
        sql += "   isnull(BudgetDetails.NewQuantity,0) AS NewQuantity, isnull(BudgetDetails.ReplaceQuantity,0) AS ReplaceQuantity,"
        sql += "  (isnull(BudgetDetails.NewQuantity,0) + isnull(BudgetDetails.ReplaceQuantity,0)) AS SumQuantity,"
        sql += "   BudgetDetails.PricePerUnit, BudgetDetails.Amount, BudgetDetails.Version, BudgetDetails.PlanYear, ActivityTypes.AssetGroup, "

        sql += "  (SELECT AppConfigs.Description FROM AppConfigs WHERE AppConfigs.Name = 'ASSET_GROUP' AND AppConfigs.Value = ActivityTypes.AssetGroup)as AssetGroupName "

        sql += "  FROM Budgetdetails INNER JOIN ActivityTypes "
        sql += "  ON Budgetdetails.ActivityType_ActivityTypeCode = ActivityTypes.ActivityTypeCode"


        sql += "   WHERE ActivityType_ActivityTypeCode Is Not NULL And"
        sql += " ((Budgetdetails.Version = '" & Session("version2") & "' And Budgetdetails.PlanYear = " & Session("year2") & ") "

        sql += " and (ActivityTypes.PlanYear = " & Session("year2") & "))"



        If Session("FCTRmode") = "1" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If

        'If Val(Session("ActivityS")) <> 0 And Val(Session("ActivityE")) <> 0 Then
        '    sql += "     and  ActivityTypes between " & Session("ActivityS") & " And " & Session("ActivityE")

        'End If

        'Add By Orn 18/08/2558
        If Session("ActivityTypeS") <> "" Then
            sql += " and ActivityType_ActivityTypeCode >= " & Session("ActivityTypeS")
        End If
        If Session("ActivityTypeE") <> "" Then
            sql += " and ActivityType_ActivityTypeCode <= " & Session("ActivityTypeE")
        End If


        If Val(Session("AssetGroup")) <> 0 Then
            sql += " and  ActivityTypes.AssetGroup = " & Session("AssetGroup")
        End If

        sql += "UNION"

        sql += "   SELECT Budgetdetails.Id, BudgetDetails.Fundcenter_FundcenterCode,case when BudgetDetails.project_projectcode is null then 'งบประมาณประจำ' else (case LEFT(BudgetDetails.project_projectcode,1) WHEN 'P' THEN 'งบประมาณโครงการ' WHEN 'D' THEN 'งบประมาณสนับสนุน' END) end as project,"
        sql += "   AssetNo AS ActivityType_ActivityTypeCode, AssetDescription AS Description,"
        sql += "   isnull(BudgetDetails.NewQuantity,0) AS NewQuantity, isnull(BudgetDetails.ReplaceQuantity,0) AS ReplaceQuantity,"
        sql += "  (isnull(BudgetDetails.NewQuantity,0) + isnull(BudgetDetails.ReplaceQuantity,0)) AS SumQuantity,"
        sql += "   BudgetDetails.PricePerUnit, BudgetDetails.Amount, BudgetDetails.Version, BudgetDetails.PlanYear, AssetRowNo AS AssetGroup, "
        sql += "  (SELECT AppConfigs.Description FROM AppConfigs WHERE AppConfigs.Name = 'ASSET_GROUP' AND AppConfigs.Value = Budgetdetails.AssetRowNo)as AssetGroupName "

        sql += "  FROM Budgetdetails "


        sql += "   WHERE ActivityType_ActivityTypeCode Is NULL And"
        sql += " Budgetdetails.Version = '" & Session("version2") & "' And Budgetdetails.PlanYear = " & Session("year2") & " "
        sql += " AND Left(AssetNo,1) = '9' "


        If Session("FCTRmode") = "1" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If



        If Val(Session("AssetGroup")) <> 0 Then
            sql += " and  AssetRowNo = " & Session("AssetGroup")
        End If
        sql += ")"

        sql += " )  A , fundcenters,FctH"
        sql += " where A.Fundcenter_FundcenterCode = fundcenters.FundcenterCode AND fundcenters.PlanYear = " & Session("year")
        sql += " AND FctH.PlanYear = " & Session("year")
        sql += " AND fundcenters.FundcenterCode = Fcth.Fundcenter_FundcenterCode "
        sql += "  GROUP BY (A.Fundcenter_FundcenterCode + ' ' + fundcenters.description) ,A.project, A.ActivityType_ActivityTypeCode, A.Description, A.Version, A.PlanYear, A.AssetGroup, A.AssetGroupName "
        sql += " ,FctH.grouppath "
        sql += " ORDER BY CONVERT(VARCHAR,FctH.grouppath) "

        'Response.Write(sql)
        ' Exit Function
        adapter.SearchReport(dt, sql)



        'Return 1
        'Exit Function
        dt.TableName = "R08DT"
        Dim das As New DataSet
        das.DataSetName = "R01DS"
        das.Tables.Add(dt)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
        'ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "Report\R01.rdlc"
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables("R08DT")))
        ReportViewer1.DocumentMapCollapsed = True

        Dim UserName As String = Session("users")
        Dim years As String = Session("year")
        Dim version As String = Session("version")
        Dim users As String = Session("users")

        Dim p1, p2, p3 As ReportParameter
        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("version", version)
        p3 = New ReportParameter("username", UserName)

        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.SetParameters(p3)


        ReportViewer1.LocalReport.Refresh()


        'Response.Write(dt.Rows.Count & sql)
        Session("count") = dt.Rows.Count



        Return dt.Rows.Count

    End Function


    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

End Class
